{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 阶段五模块四作业"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据加载， pd.read_excel('./18级高一体测成绩汇总.xls')默认加载第一个工作表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import random"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'13</td>\n",
       "      <td>8.88</td>\n",
       "      <td>195.0</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>2785</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'16</td>\n",
       "      <td>7.70</td>\n",
       "      <td>225.0</td>\n",
       "      <td>11</td>\n",
       "      <td>7</td>\n",
       "      <td>3133</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'09</td>\n",
       "      <td>8.45</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>3901</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'21</td>\n",
       "      <td>8.05</td>\n",
       "      <td>206.0</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>4946</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3'44</td>\n",
       "      <td>7.52</td>\n",
       "      <td>210.0</td>\n",
       "      <td>13</td>\n",
       "      <td>9</td>\n",
       "      <td>3538</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3'49</td>\n",
       "      <td>7.94</td>\n",
       "      <td>190.0</td>\n",
       "      <td>20</td>\n",
       "      <td>7</td>\n",
       "      <td>3970</td>\n",
       "      <td>175.0</td>\n",
       "      <td>66.4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3'54</td>\n",
       "      <td>7.75</td>\n",
       "      <td>186.0</td>\n",
       "      <td>11</td>\n",
       "      <td>7</td>\n",
       "      <td>3710</td>\n",
       "      <td>173.0</td>\n",
       "      <td>53.9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'3</td>\n",
       "      <td>8.06</td>\n",
       "      <td>195.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>5578</td>\n",
       "      <td>178.0</td>\n",
       "      <td>83.1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'01</td>\n",
       "      <td>7.75</td>\n",
       "      <td>220.0</td>\n",
       "      <td>15</td>\n",
       "      <td>10</td>\n",
       "      <td>3821</td>\n",
       "      <td>175.0</td>\n",
       "      <td>66.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'12</td>\n",
       "      <td>7.38</td>\n",
       "      <td>245.0</td>\n",
       "      <td>17</td>\n",
       "      <td>11</td>\n",
       "      <td>4423</td>\n",
       "      <td>167.0</td>\n",
       "      <td>53.9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4</td>\n",
       "      <td>7.82</td>\n",
       "      <td>219.0</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>4031</td>\n",
       "      <td>173.0</td>\n",
       "      <td>57.4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'13</td>\n",
       "      <td>7.37</td>\n",
       "      <td>228.0</td>\n",
       "      <td>9</td>\n",
       "      <td>15</td>\n",
       "      <td>4354</td>\n",
       "      <td>163.0</td>\n",
       "      <td>54.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3'45</td>\n",
       "      <td>7.66</td>\n",
       "      <td>202.0</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>2238</td>\n",
       "      <td>179.0</td>\n",
       "      <td>61.1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3'46</td>\n",
       "      <td>7.66</td>\n",
       "      <td>245.0</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>4811</td>\n",
       "      <td>177.0</td>\n",
       "      <td>63.9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3'57</td>\n",
       "      <td>7.60</td>\n",
       "      <td>192.0</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>4147</td>\n",
       "      <td>174.0</td>\n",
       "      <td>59.2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'18</td>\n",
       "      <td>8.14</td>\n",
       "      <td>210.0</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>4241</td>\n",
       "      <td>179.0</td>\n",
       "      <td>61.9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3'32</td>\n",
       "      <td>7.20</td>\n",
       "      <td>255.0</td>\n",
       "      <td>22</td>\n",
       "      <td>12</td>\n",
       "      <td>5324</td>\n",
       "      <td>183.0</td>\n",
       "      <td>63.4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3'56</td>\n",
       "      <td>8.15</td>\n",
       "      <td>207.0</td>\n",
       "      <td>13</td>\n",
       "      <td>12</td>\n",
       "      <td>4363</td>\n",
       "      <td>173.0</td>\n",
       "      <td>60.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3'47</td>\n",
       "      <td>8.15</td>\n",
       "      <td>202.0</td>\n",
       "      <td>13</td>\n",
       "      <td>16</td>\n",
       "      <td>5364</td>\n",
       "      <td>174.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3'53</td>\n",
       "      <td>7.85</td>\n",
       "      <td>210.0</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>3445</td>\n",
       "      <td>177.0</td>\n",
       "      <td>56.9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3'57</td>\n",
       "      <td>7.85</td>\n",
       "      <td>220.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>5670</td>\n",
       "      <td>177.0</td>\n",
       "      <td>55.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3'42</td>\n",
       "      <td>7.23</td>\n",
       "      <td>212.0</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "      <td>5709</td>\n",
       "      <td>185.0</td>\n",
       "      <td>72.3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'3</td>\n",
       "      <td>7.68</td>\n",
       "      <td>218.0</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>4780</td>\n",
       "      <td>177.0</td>\n",
       "      <td>83.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'14</td>\n",
       "      <td>8.30</td>\n",
       "      <td>206.0</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>3358</td>\n",
       "      <td>173.0</td>\n",
       "      <td>46.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'04</td>\n",
       "      <td>8.15</td>\n",
       "      <td>205.0</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>3494</td>\n",
       "      <td>169.0</td>\n",
       "      <td>48.3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'04</td>\n",
       "      <td>7.55</td>\n",
       "      <td>190.0</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>3286</td>\n",
       "      <td>169.0</td>\n",
       "      <td>50.1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'02</td>\n",
       "      <td>7.55</td>\n",
       "      <td>240.0</td>\n",
       "      <td>5</td>\n",
       "      <td>12</td>\n",
       "      <td>4483</td>\n",
       "      <td>171.0</td>\n",
       "      <td>58.4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3'57</td>\n",
       "      <td>7.89</td>\n",
       "      <td>220.0</td>\n",
       "      <td>9</td>\n",
       "      <td>11</td>\n",
       "      <td>4254</td>\n",
       "      <td>166.0</td>\n",
       "      <td>54.8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'16</td>\n",
       "      <td>8.19</td>\n",
       "      <td>212.0</td>\n",
       "      <td>27</td>\n",
       "      <td>7</td>\n",
       "      <td>3498</td>\n",
       "      <td>169.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>447</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4'15</td>\n",
       "      <td>8.36</td>\n",
       "      <td>217.0</td>\n",
       "      <td>20</td>\n",
       "      <td>2</td>\n",
       "      <td>5452</td>\n",
       "      <td>175.0</td>\n",
       "      <td>83.4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>448</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4'36</td>\n",
       "      <td>7.22</td>\n",
       "      <td>267.0</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>5555</td>\n",
       "      <td>179.0</td>\n",
       "      <td>62.2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3'48</td>\n",
       "      <td>7.37</td>\n",
       "      <td>225.0</td>\n",
       "      <td>17</td>\n",
       "      <td>12</td>\n",
       "      <td>5519</td>\n",
       "      <td>176.0</td>\n",
       "      <td>62.2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3'58</td>\n",
       "      <td>7.37</td>\n",
       "      <td>236.0</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>4246</td>\n",
       "      <td>169.0</td>\n",
       "      <td>60.1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>451</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4'02</td>\n",
       "      <td>8.00</td>\n",
       "      <td>210.0</td>\n",
       "      <td>18</td>\n",
       "      <td>7</td>\n",
       "      <td>4034</td>\n",
       "      <td>167.0</td>\n",
       "      <td>56.8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>452</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4'02</td>\n",
       "      <td>8.00</td>\n",
       "      <td>196.0</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>5738</td>\n",
       "      <td>172.0</td>\n",
       "      <td>66.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>453</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4'38</td>\n",
       "      <td>8.09</td>\n",
       "      <td>223.0</td>\n",
       "      <td>21</td>\n",
       "      <td>8</td>\n",
       "      <td>5168</td>\n",
       "      <td>169.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>454</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>455</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4'2</td>\n",
       "      <td>8.37</td>\n",
       "      <td>208.0</td>\n",
       "      <td>21</td>\n",
       "      <td>8</td>\n",
       "      <td>5677</td>\n",
       "      <td>172.0</td>\n",
       "      <td>63.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>456</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4'26</td>\n",
       "      <td>7.89</td>\n",
       "      <td>232.0</td>\n",
       "      <td>21</td>\n",
       "      <td>8</td>\n",
       "      <td>7052</td>\n",
       "      <td>180.0</td>\n",
       "      <td>82.9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>457</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4'09</td>\n",
       "      <td>8.46</td>\n",
       "      <td>205.0</td>\n",
       "      <td>15</td>\n",
       "      <td>7</td>\n",
       "      <td>4208</td>\n",
       "      <td>171.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3'49</td>\n",
       "      <td>7.66</td>\n",
       "      <td>232.0</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>5897</td>\n",
       "      <td>175.0</td>\n",
       "      <td>56.1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>459</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4'36</td>\n",
       "      <td>7.77</td>\n",
       "      <td>236.0</td>\n",
       "      <td>11</td>\n",
       "      <td>20</td>\n",
       "      <td>5158</td>\n",
       "      <td>176.0</td>\n",
       "      <td>55.2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>460</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4'37</td>\n",
       "      <td>8.27</td>\n",
       "      <td>208.0</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>6311</td>\n",
       "      <td>177.0</td>\n",
       "      <td>95.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>461</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3'44</td>\n",
       "      <td>8.27</td>\n",
       "      <td>217.0</td>\n",
       "      <td>15</td>\n",
       "      <td>7</td>\n",
       "      <td>5075</td>\n",
       "      <td>170.0</td>\n",
       "      <td>57.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>462</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3'55</td>\n",
       "      <td>7.98</td>\n",
       "      <td>212.0</td>\n",
       "      <td>20</td>\n",
       "      <td>10</td>\n",
       "      <td>5564</td>\n",
       "      <td>168.0</td>\n",
       "      <td>54.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>463</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3'41</td>\n",
       "      <td>7.57</td>\n",
       "      <td>225.0</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>5599</td>\n",
       "      <td>181.0</td>\n",
       "      <td>74.8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>5'29</td>\n",
       "      <td>9.02</td>\n",
       "      <td>210.0</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>6712</td>\n",
       "      <td>183.0</td>\n",
       "      <td>95.9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>465</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4'11</td>\n",
       "      <td>7.51</td>\n",
       "      <td>238.0</td>\n",
       "      <td>21</td>\n",
       "      <td>14</td>\n",
       "      <td>5590</td>\n",
       "      <td>179.0</td>\n",
       "      <td>67.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>466</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4'56</td>\n",
       "      <td>7.42</td>\n",
       "      <td>252.0</td>\n",
       "      <td>9</td>\n",
       "      <td>13</td>\n",
       "      <td>5159</td>\n",
       "      <td>180.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>467</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3'54</td>\n",
       "      <td>7.96</td>\n",
       "      <td>229.0</td>\n",
       "      <td>14</td>\n",
       "      <td>9</td>\n",
       "      <td>5254</td>\n",
       "      <td>182.0</td>\n",
       "      <td>64.1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>468</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>469</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4'4</td>\n",
       "      <td>8.02</td>\n",
       "      <td>180.0</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>4592</td>\n",
       "      <td>187.0</td>\n",
       "      <td>64.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>470</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3'54</td>\n",
       "      <td>7.51</td>\n",
       "      <td>238.0</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>5572</td>\n",
       "      <td>176.0</td>\n",
       "      <td>59.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>471</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4'58</td>\n",
       "      <td>8.76</td>\n",
       "      <td>200.0</td>\n",
       "      <td>12</td>\n",
       "      <td>9</td>\n",
       "      <td>4533</td>\n",
       "      <td>169.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4'23</td>\n",
       "      <td>8.27</td>\n",
       "      <td>208.0</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>4647</td>\n",
       "      <td>176.0</td>\n",
       "      <td>69.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>5'19</td>\n",
       "      <td>9.55</td>\n",
       "      <td>210.0</td>\n",
       "      <td>15</td>\n",
       "      <td>6</td>\n",
       "      <td>7042</td>\n",
       "      <td>177.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3'25</td>\n",
       "      <td>7.50</td>\n",
       "      <td>252.0</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>5755</td>\n",
       "      <td>181.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4'39</td>\n",
       "      <td>7.81</td>\n",
       "      <td>208.0</td>\n",
       "      <td>14</td>\n",
       "      <td>11</td>\n",
       "      <td>5688</td>\n",
       "      <td>172.0</td>\n",
       "      <td>51.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>477 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别 男1000米跑  男50米跑    男跳远  男体前屈  男引体  男肺活量     身高    体重  BMI\n",
       "0     1  男    4'13   8.88  195.0    12    1  2785  170.0  72.6    0\n",
       "1     1  男    4'16   7.70  225.0    11    7  3133  174.0  52.7    0\n",
       "2     1  男    4'09   8.45  218.0    14    1  3901  169.0  46.5    0\n",
       "3     1  男    4'21   8.05  206.0    13    1  4946  183.0  79.7    0\n",
       "4     1  男    3'44   7.52  210.0    13    9  3538  171.0  54.7    0\n",
       "5     1  男    3'49   7.94  190.0    20    7  3970  175.0  66.4    0\n",
       "6     1  男    3'54   7.75  186.0    11    7  3710  173.0  53.9    0\n",
       "7     1  男     4'3   8.06  195.0     3    1  5578  178.0  83.1    0\n",
       "8     1  男    4'01   7.75  220.0    15   10  3821  175.0  66.5    0\n",
       "9     1  男    4'12   7.38  245.0    17   11  4423  167.0  53.9    0\n",
       "10    1  男       4   7.82  219.0    13   11  4031  173.0  57.4    0\n",
       "11    1  男    4'13   7.37  228.0     9   15  4354  163.0  54.6    0\n",
       "12    1  男    3'45   7.66  202.0     7    3  2238  179.0  61.1    0\n",
       "13    1  男    3'46   7.66  245.0     3    7  4811  177.0  63.9    0\n",
       "14    1  男       0   0.00    0.0     0    0     0    0.0   0.0    0\n",
       "15    1  男    3'57   7.60  192.0     7    5  4147  174.0  59.2    0\n",
       "16    1  男    4'18   8.14  210.0     8    4  4241  179.0  61.9    0\n",
       "17    1  男    3'32   7.20  255.0    22   12  5324  183.0  63.4    0\n",
       "18    1  男    3'56   8.15  207.0    13   12  4363  173.0  60.5    0\n",
       "19    1  男    3'47   8.15  202.0    13   16  5364  174.0  56.0    0\n",
       "20    1  男    3'53   7.85  210.0     3    7  3445  177.0  56.9    0\n",
       "21    1  男    3'57   7.85  220.0     9    2  5670  177.0  55.5    0\n",
       "22    1  男    3'42   7.23  212.0    12   15  5709  185.0  72.3    0\n",
       "23    1  男     4'3   7.68  218.0    15    3  4780  177.0  83.7    0\n",
       "24    1  男    4'14   8.30  206.0    15    1  3358  173.0  46.6    0\n",
       "25    1  男    4'04   8.15  205.0     9    5  3494  169.0  48.3    0\n",
       "26    1  男    4'04   7.55  190.0    12    5  3286  169.0  50.1    0\n",
       "27    1  男    4'02   7.55  240.0     5   12  4483  171.0  58.4    0\n",
       "28    1  男    3'57   7.89  220.0     9   11  4254  166.0  54.8    0\n",
       "29    1  男    4'16   8.19  212.0    27    7  3498  169.0  68.0    0\n",
       "..   .. ..     ...    ...    ...   ...  ...   ...    ...   ...  ...\n",
       "447  17  男    4'15   8.36  217.0    20    2  5452  175.0  83.4    0\n",
       "448  17  男    4'36   7.22  267.0     6   11  5555  179.0  62.2    0\n",
       "449  17  男    3'48   7.37  225.0    17   12  5519  176.0  62.2    0\n",
       "450  17  男    3'58   7.37  236.0    12   11  4246  169.0  60.1    0\n",
       "451  17  男    4'02   8.00  210.0    18    7  4034  167.0  56.8    0\n",
       "452  17  男    4'02   8.00  196.0    12    4  5738  172.0  66.5    0\n",
       "453  17  男    4'38   8.09  223.0    21    8  5168  169.0  78.0    0\n",
       "454  17  男       0   0.00    0.0     0    0     0    0.0   0.0    0\n",
       "455  17  男     4'2   8.37  208.0    21    8  5677  172.0  63.7    0\n",
       "456  17  男    4'26   7.89  232.0    21    8  7052  180.0  82.9    0\n",
       "457  17  男    4'09   8.46  205.0    15    7  4208  171.0  61.0    0\n",
       "458  17  男    3'49   7.66  232.0    11   10  5897  175.0  56.1    0\n",
       "459  17  男    4'36   7.77  236.0    11   20  5158  176.0  55.2    0\n",
       "460  17  男    4'37   8.27  208.0    17    1  6311  177.0  95.6    0\n",
       "461  17  男    3'44   8.27  217.0    15    7  5075  170.0  57.6    0\n",
       "462  17  男    3'55   7.98  212.0    20   10  5564  168.0  54.5    0\n",
       "463  17  男    3'41   7.57  225.0     9    5  5599  181.0  74.8    0\n",
       "464  17  男    5'29   9.02  210.0    12    0  6712  183.0  95.9    0\n",
       "465  17  男    4'11   7.51  238.0    21   14  5590  179.0  67.7    0\n",
       "466  17  男    4'56   7.42  252.0     9   13  5159  180.0  70.0    0\n",
       "467  17  男    3'54   7.96  229.0    14    9  5254  182.0  64.1    0\n",
       "468  17  男       0   0.00    0.0     0    0     0    0.0   0.0    0\n",
       "469  17  男     4'4   8.02  180.0     8    1  4592  187.0  64.6    0\n",
       "470  17  男    3'54   7.51  238.0    13   11  5572  176.0  59.5    0\n",
       "471  17  男    4'58   8.76  200.0    12    9  4533  169.0  51.3    0\n",
       "472  17  男    4'23   8.27  208.0    10    0  4647  176.0  69.5    0\n",
       "473  17  男    5'19   9.55  210.0    15    6  7042  177.0  76.0    0\n",
       "474  17  男    3'25   7.50  252.0    13   13  5755  181.0  65.0    0\n",
       "475  17  男    4'39   7.81  208.0    14   11  5688  172.0  51.7    0\n",
       "476  17  男       0   0.00    0.0     0    0     0    0.0   0.0    0\n",
       "\n",
       "[477 rows x 11 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.read_excel('./18级高一体测成绩汇总.xls',sheetname=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据加载， pd.read_excel('./18级高一体测成绩汇总.xls',sheet_name = 1)指定加载第二个工作表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>9.32</td>\n",
       "      <td>185.0</td>\n",
       "      <td>16</td>\n",
       "      <td>48</td>\n",
       "      <td>3775</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>11.44</td>\n",
       "      <td>148.0</td>\n",
       "      <td>9</td>\n",
       "      <td>29</td>\n",
       "      <td>3683</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>13.40</td>\n",
       "      <td>150.0</td>\n",
       "      <td>7</td>\n",
       "      <td>40</td>\n",
       "      <td>3331</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>9.52</td>\n",
       "      <td>172.0</td>\n",
       "      <td>21</td>\n",
       "      <td>46</td>\n",
       "      <td>3701</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>9.79</td>\n",
       "      <td>145.0</td>\n",
       "      <td>8</td>\n",
       "      <td>34</td>\n",
       "      <td>3592</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.47</td>\n",
       "      <td>10.01</td>\n",
       "      <td>158.0</td>\n",
       "      <td>17</td>\n",
       "      <td>35</td>\n",
       "      <td>3483</td>\n",
       "      <td>170.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.69</td>\n",
       "      <td>10.42</td>\n",
       "      <td>150.0</td>\n",
       "      <td>18</td>\n",
       "      <td>32</td>\n",
       "      <td>3754</td>\n",
       "      <td>158.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.26</td>\n",
       "      <td>10.03</td>\n",
       "      <td>165.0</td>\n",
       "      <td>16</td>\n",
       "      <td>35</td>\n",
       "      <td>4520</td>\n",
       "      <td>160.0</td>\n",
       "      <td>48.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.48</td>\n",
       "      <td>10.59</td>\n",
       "      <td>159.0</td>\n",
       "      <td>8</td>\n",
       "      <td>46</td>\n",
       "      <td>3662</td>\n",
       "      <td>160.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.18</td>\n",
       "      <td>10.30</td>\n",
       "      <td>161.0</td>\n",
       "      <td>22</td>\n",
       "      <td>42</td>\n",
       "      <td>3955</td>\n",
       "      <td>171.0</td>\n",
       "      <td>62.2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.38</td>\n",
       "      <td>9.37</td>\n",
       "      <td>162.0</td>\n",
       "      <td>15</td>\n",
       "      <td>43</td>\n",
       "      <td>4374</td>\n",
       "      <td>157.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.11</td>\n",
       "      <td>10.68</td>\n",
       "      <td>150.0</td>\n",
       "      <td>15</td>\n",
       "      <td>45</td>\n",
       "      <td>4488</td>\n",
       "      <td>175.0</td>\n",
       "      <td>53.2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.45</td>\n",
       "      <td>9.74</td>\n",
       "      <td>168.0</td>\n",
       "      <td>21</td>\n",
       "      <td>35</td>\n",
       "      <td>4176</td>\n",
       "      <td>164.0</td>\n",
       "      <td>49.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.59</td>\n",
       "      <td>10.83</td>\n",
       "      <td>140.0</td>\n",
       "      <td>6</td>\n",
       "      <td>41</td>\n",
       "      <td>3497</td>\n",
       "      <td>159.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.32</td>\n",
       "      <td>10.25</td>\n",
       "      <td>167.0</td>\n",
       "      <td>19</td>\n",
       "      <td>31</td>\n",
       "      <td>4094</td>\n",
       "      <td>165.0</td>\n",
       "      <td>56.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.40</td>\n",
       "      <td>10.25</td>\n",
       "      <td>152.0</td>\n",
       "      <td>21</td>\n",
       "      <td>43</td>\n",
       "      <td>3403</td>\n",
       "      <td>156.0</td>\n",
       "      <td>50.9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.58</td>\n",
       "      <td>9.85</td>\n",
       "      <td>160.0</td>\n",
       "      <td>14</td>\n",
       "      <td>29</td>\n",
       "      <td>4433</td>\n",
       "      <td>172.0</td>\n",
       "      <td>77.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.36</td>\n",
       "      <td>9.41</td>\n",
       "      <td>182.0</td>\n",
       "      <td>24</td>\n",
       "      <td>30</td>\n",
       "      <td>4520</td>\n",
       "      <td>167.0</td>\n",
       "      <td>61.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.48</td>\n",
       "      <td>9.44</td>\n",
       "      <td>176.0</td>\n",
       "      <td>17</td>\n",
       "      <td>43</td>\n",
       "      <td>4209</td>\n",
       "      <td>157.0</td>\n",
       "      <td>57.1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.24</td>\n",
       "      <td>9.07</td>\n",
       "      <td>185.0</td>\n",
       "      <td>22</td>\n",
       "      <td>33</td>\n",
       "      <td>2887</td>\n",
       "      <td>167.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.48</td>\n",
       "      <td>10.00</td>\n",
       "      <td>160.0</td>\n",
       "      <td>18</td>\n",
       "      <td>30</td>\n",
       "      <td>3016</td>\n",
       "      <td>157.0</td>\n",
       "      <td>55.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.09</td>\n",
       "      <td>10.00</td>\n",
       "      <td>172.0</td>\n",
       "      <td>8</td>\n",
       "      <td>42</td>\n",
       "      <td>3949</td>\n",
       "      <td>172.0</td>\n",
       "      <td>57.8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.30</td>\n",
       "      <td>9.74</td>\n",
       "      <td>155.0</td>\n",
       "      <td>10</td>\n",
       "      <td>35</td>\n",
       "      <td>3574</td>\n",
       "      <td>155.0</td>\n",
       "      <td>52.2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.35</td>\n",
       "      <td>9.74</td>\n",
       "      <td>180.0</td>\n",
       "      <td>19</td>\n",
       "      <td>33</td>\n",
       "      <td>3285</td>\n",
       "      <td>161.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.03</td>\n",
       "      <td>10.80</td>\n",
       "      <td>151.0</td>\n",
       "      <td>11</td>\n",
       "      <td>40</td>\n",
       "      <td>3503</td>\n",
       "      <td>162.0</td>\n",
       "      <td>50.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.40</td>\n",
       "      <td>9.95</td>\n",
       "      <td>168.0</td>\n",
       "      <td>15</td>\n",
       "      <td>41</td>\n",
       "      <td>4586</td>\n",
       "      <td>166.0</td>\n",
       "      <td>53.3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.58</td>\n",
       "      <td>9.68</td>\n",
       "      <td>186.0</td>\n",
       "      <td>21</td>\n",
       "      <td>40</td>\n",
       "      <td>3942</td>\n",
       "      <td>161.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.49</td>\n",
       "      <td>8.77</td>\n",
       "      <td>185.0</td>\n",
       "      <td>19</td>\n",
       "      <td>40</td>\n",
       "      <td>3171</td>\n",
       "      <td>160.0</td>\n",
       "      <td>46.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2</td>\n",
       "      <td>女</td>\n",
       "      <td>0.00</td>\n",
       "      <td>10.10</td>\n",
       "      <td>150.0</td>\n",
       "      <td>30</td>\n",
       "      <td>28</td>\n",
       "      <td>2673</td>\n",
       "      <td>169.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2</td>\n",
       "      <td>女</td>\n",
       "      <td>4.19</td>\n",
       "      <td>9.90</td>\n",
       "      <td>151.0</td>\n",
       "      <td>20</td>\n",
       "      <td>32</td>\n",
       "      <td>3410</td>\n",
       "      <td>165.0</td>\n",
       "      <td>65.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>563</th>\n",
       "      <td>16</td>\n",
       "      <td>女</td>\n",
       "      <td>4.12</td>\n",
       "      <td>10.09</td>\n",
       "      <td>162.0</td>\n",
       "      <td>15</td>\n",
       "      <td>48</td>\n",
       "      <td>2802</td>\n",
       "      <td>161.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>564</th>\n",
       "      <td>16</td>\n",
       "      <td>女</td>\n",
       "      <td>3.36</td>\n",
       "      <td>9.35</td>\n",
       "      <td>182.0</td>\n",
       "      <td>15</td>\n",
       "      <td>40</td>\n",
       "      <td>2390</td>\n",
       "      <td>165.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>565</th>\n",
       "      <td>16</td>\n",
       "      <td>女</td>\n",
       "      <td>4.01</td>\n",
       "      <td>9.97</td>\n",
       "      <td>156.0</td>\n",
       "      <td>12</td>\n",
       "      <td>40</td>\n",
       "      <td>2668</td>\n",
       "      <td>167.0</td>\n",
       "      <td>51.1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>566</th>\n",
       "      <td>16</td>\n",
       "      <td>女</td>\n",
       "      <td>3.44</td>\n",
       "      <td>9.97</td>\n",
       "      <td>160.0</td>\n",
       "      <td>15</td>\n",
       "      <td>35</td>\n",
       "      <td>3437</td>\n",
       "      <td>159.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>567</th>\n",
       "      <td>16</td>\n",
       "      <td>女</td>\n",
       "      <td>5.17</td>\n",
       "      <td>11.77</td>\n",
       "      <td>130.0</td>\n",
       "      <td>12</td>\n",
       "      <td>33</td>\n",
       "      <td>3447</td>\n",
       "      <td>163.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>568</th>\n",
       "      <td>16</td>\n",
       "      <td>女</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>569</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.31</td>\n",
       "      <td>9.44</td>\n",
       "      <td>180.0</td>\n",
       "      <td>13</td>\n",
       "      <td>58</td>\n",
       "      <td>1923</td>\n",
       "      <td>165.0</td>\n",
       "      <td>43.8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>570</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>0.00</td>\n",
       "      <td>9.21</td>\n",
       "      <td>176.0</td>\n",
       "      <td>13</td>\n",
       "      <td>41</td>\n",
       "      <td>2091</td>\n",
       "      <td>158.0</td>\n",
       "      <td>52.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>571</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.56</td>\n",
       "      <td>10.53</td>\n",
       "      <td>162.0</td>\n",
       "      <td>16</td>\n",
       "      <td>48</td>\n",
       "      <td>1918</td>\n",
       "      <td>155.0</td>\n",
       "      <td>45.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>572</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.37</td>\n",
       "      <td>10.08</td>\n",
       "      <td>179.0</td>\n",
       "      <td>14</td>\n",
       "      <td>48</td>\n",
       "      <td>2300</td>\n",
       "      <td>154.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>573</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.54</td>\n",
       "      <td>11.14</td>\n",
       "      <td>140.0</td>\n",
       "      <td>14</td>\n",
       "      <td>39</td>\n",
       "      <td>2453</td>\n",
       "      <td>162.0</td>\n",
       "      <td>52.1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>574</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.44</td>\n",
       "      <td>9.83</td>\n",
       "      <td>168.0</td>\n",
       "      <td>22</td>\n",
       "      <td>32</td>\n",
       "      <td>3571</td>\n",
       "      <td>165.0</td>\n",
       "      <td>59.3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>575</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.25</td>\n",
       "      <td>10.00</td>\n",
       "      <td>150.0</td>\n",
       "      <td>15</td>\n",
       "      <td>38</td>\n",
       "      <td>2489</td>\n",
       "      <td>161.0</td>\n",
       "      <td>65.3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>576</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.23</td>\n",
       "      <td>10.00</td>\n",
       "      <td>170.0</td>\n",
       "      <td>26</td>\n",
       "      <td>38</td>\n",
       "      <td>2528</td>\n",
       "      <td>161.0</td>\n",
       "      <td>52.2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>577</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.55</td>\n",
       "      <td>10.13</td>\n",
       "      <td>162.0</td>\n",
       "      <td>16</td>\n",
       "      <td>43</td>\n",
       "      <td>2625</td>\n",
       "      <td>156.0</td>\n",
       "      <td>61.4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>578</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.05</td>\n",
       "      <td>10.20</td>\n",
       "      <td>160.0</td>\n",
       "      <td>18</td>\n",
       "      <td>47</td>\n",
       "      <td>2654</td>\n",
       "      <td>149.0</td>\n",
       "      <td>46.2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>579</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.10</td>\n",
       "      <td>10.15</td>\n",
       "      <td>162.0</td>\n",
       "      <td>5</td>\n",
       "      <td>40</td>\n",
       "      <td>2839</td>\n",
       "      <td>161.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>580</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.25</td>\n",
       "      <td>10.46</td>\n",
       "      <td>160.0</td>\n",
       "      <td>13</td>\n",
       "      <td>40</td>\n",
       "      <td>1924</td>\n",
       "      <td>156.0</td>\n",
       "      <td>51.1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>581</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.02</td>\n",
       "      <td>9.82</td>\n",
       "      <td>165.0</td>\n",
       "      <td>13</td>\n",
       "      <td>38</td>\n",
       "      <td>2741</td>\n",
       "      <td>167.0</td>\n",
       "      <td>58.2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>582</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.25</td>\n",
       "      <td>9.28</td>\n",
       "      <td>167.0</td>\n",
       "      <td>11</td>\n",
       "      <td>47</td>\n",
       "      <td>1838</td>\n",
       "      <td>172.0</td>\n",
       "      <td>51.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>583</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.56</td>\n",
       "      <td>11.03</td>\n",
       "      <td>162.0</td>\n",
       "      <td>11</td>\n",
       "      <td>37</td>\n",
       "      <td>2008</td>\n",
       "      <td>160.0</td>\n",
       "      <td>42.2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>584</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.54</td>\n",
       "      <td>9.16</td>\n",
       "      <td>182.0</td>\n",
       "      <td>23</td>\n",
       "      <td>41</td>\n",
       "      <td>1881</td>\n",
       "      <td>160.0</td>\n",
       "      <td>56.8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>585</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.37</td>\n",
       "      <td>9.22</td>\n",
       "      <td>175.0</td>\n",
       "      <td>11</td>\n",
       "      <td>46</td>\n",
       "      <td>2167</td>\n",
       "      <td>158.0</td>\n",
       "      <td>50.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>586</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>0.00</td>\n",
       "      <td>11.15</td>\n",
       "      <td>149.0</td>\n",
       "      <td>28</td>\n",
       "      <td>50</td>\n",
       "      <td>2498</td>\n",
       "      <td>158.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>587</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>9.60</td>\n",
       "      <td>170.0</td>\n",
       "      <td>6</td>\n",
       "      <td>41</td>\n",
       "      <td>2824</td>\n",
       "      <td>163.0</td>\n",
       "      <td>59.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>588</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.51</td>\n",
       "      <td>9.60</td>\n",
       "      <td>150.0</td>\n",
       "      <td>24</td>\n",
       "      <td>41</td>\n",
       "      <td>2255</td>\n",
       "      <td>158.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>589</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.00</td>\n",
       "      <td>10.18</td>\n",
       "      <td>150.0</td>\n",
       "      <td>13</td>\n",
       "      <td>36</td>\n",
       "      <td>2937</td>\n",
       "      <td>161.0</td>\n",
       "      <td>55.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.45</td>\n",
       "      <td>10.18</td>\n",
       "      <td>152.0</td>\n",
       "      <td>15</td>\n",
       "      <td>35</td>\n",
       "      <td>2592</td>\n",
       "      <td>165.0</td>\n",
       "      <td>48.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>591</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.01</td>\n",
       "      <td>9.67</td>\n",
       "      <td>165.0</td>\n",
       "      <td>10</td>\n",
       "      <td>41</td>\n",
       "      <td>1829</td>\n",
       "      <td>154.0</td>\n",
       "      <td>43.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>592</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.48</td>\n",
       "      <td>9.09</td>\n",
       "      <td>180.0</td>\n",
       "      <td>10</td>\n",
       "      <td>46</td>\n",
       "      <td>2962</td>\n",
       "      <td>162.0</td>\n",
       "      <td>55.3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>593 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  女800米跑  女50米跑    女跳远  女体前屈  女仰卧  女肺活量     身高    体重  BMI\n",
       "0     1  女    3.22   9.32  185.0    16   48  3775  163.0  51.3    0\n",
       "1     1  女    4.59  11.44  148.0     9   29  3683  163.0  66.6    0\n",
       "2     1  女    3.46  13.40  150.0     7   40  3331  157.0  60.0    0\n",
       "3     1  女    3.39   9.52  172.0    21   46  3701  160.0  50.7    0\n",
       "4     1  女    3.43   9.79  145.0     8   34  3592  167.0  63.9    0\n",
       "5     1  女    3.47  10.01  158.0    17   35  3483  170.0  47.0    0\n",
       "6     1  女    4.69  10.42  150.0    18   32  3754  158.0  54.0    0\n",
       "7     1  女    4.26  10.03  165.0    16   35  4520  160.0  48.5    0\n",
       "8     1  女    3.48  10.59  159.0     8   46  3662  160.0  42.0    0\n",
       "9     1  女    4.18  10.30  161.0    22   42  3955  171.0  62.2    0\n",
       "10    1  女    3.38   9.37  162.0    15   43  4374  157.0  56.0    0\n",
       "11    1  女    4.11  10.68  150.0    15   45  4488  175.0  53.2    0\n",
       "12    1  女    3.45   9.74  168.0    21   35  4176  164.0  49.5    0\n",
       "13    1  女    3.59  10.83  140.0     6   41  3497  159.0  52.0    0\n",
       "14    1  女    3.32  10.25  167.0    19   31  4094  165.0  56.7    0\n",
       "15    1  女    3.40  10.25  152.0    21   43  3403  156.0  50.9    0\n",
       "16    1  女    3.58   9.85  160.0    14   29  4433  172.0  77.6    0\n",
       "17    1  女    3.36   9.41  182.0    24   30  4520  167.0  61.6    0\n",
       "18    1  女    3.48   9.44  176.0    17   43  4209  157.0  57.1    0\n",
       "19    1  女    3.24   9.07  185.0    22   33  2887  167.0  52.0    0\n",
       "20    1  女    3.48  10.00  160.0    18   30  3016  157.0  55.7    0\n",
       "21    1  女    4.09  10.00  172.0     8   42  3949  172.0  57.8    0\n",
       "22    1  女    3.30   9.74  155.0    10   35  3574  155.0  52.2    0\n",
       "23    1  女    3.35   9.74  180.0    19   33  3285  161.0  51.3    0\n",
       "24    1  女    4.03  10.80  151.0    11   40  3503  162.0  50.6    0\n",
       "25    1  女    3.40   9.95  168.0    15   41  4586  166.0  53.3    0\n",
       "26    1  女    3.58   9.68  186.0    21   40  3942  161.0  51.3    0\n",
       "27    1  女    3.49   8.77  185.0    19   40  3171  160.0  46.7    0\n",
       "28    2  女    0.00  10.10  150.0    30   28  2673  169.0  61.0    0\n",
       "29    2  女    4.19   9.90  151.0    20   32  3410  165.0  65.6    0\n",
       "..   .. ..     ...    ...    ...   ...  ...   ...    ...   ...  ...\n",
       "563  16  女    4.12  10.09  162.0    15   48  2802  161.0  54.0    0\n",
       "564  16  女    3.36   9.35  182.0    15   40  2390  165.0  53.0    0\n",
       "565  16  女    4.01   9.97  156.0    12   40  2668  167.0  51.1    0\n",
       "566  16  女    3.44   9.97  160.0    15   35  3437  159.0  58.0    0\n",
       "567  16  女    5.17  11.77  130.0    12   33  3447  163.0  76.0    0\n",
       "568  16  女    0.00   0.00    0.0     0    0     0    0.0   0.0    0\n",
       "569  17  女    3.31   9.44  180.0    13   58  1923  165.0  43.8    0\n",
       "570  17  女    0.00   9.21  176.0    13   41  2091  158.0  52.5    0\n",
       "571  17  女    3.56  10.53  162.0    16   48  1918  155.0  45.7    0\n",
       "572  17  女    3.37  10.08  179.0    14   48  2300  154.0  45.0    0\n",
       "573  17  女    4.54  11.14  140.0    14   39  2453  162.0  52.1    0\n",
       "574  17  女    3.44   9.83  168.0    22   32  3571  165.0  59.3    0\n",
       "575  17  女    4.25  10.00  150.0    15   38  2489  161.0  65.3    0\n",
       "576  17  女    4.23  10.00  170.0    26   38  2528  161.0  52.2    0\n",
       "577  17  女    3.55  10.13  162.0    16   43  2625  156.0  61.4    0\n",
       "578  17  女    4.05  10.20  160.0    18   47  2654  149.0  46.2    0\n",
       "579  17  女    4.10  10.15  162.0     5   40  2839  161.0  59.0    0\n",
       "580  17  女    3.25  10.46  160.0    13   40  1924  156.0  51.1    0\n",
       "581  17  女    4.02   9.82  165.0    13   38  2741  167.0  58.2    0\n",
       "582  17  女    3.25   9.28  167.0    11   47  1838  172.0  51.7    0\n",
       "583  17  女    3.56  11.03  162.0    11   37  2008  160.0  42.2    0\n",
       "584  17  女    3.54   9.16  182.0    23   41  1881  160.0  56.8    0\n",
       "585  17  女    3.37   9.22  175.0    11   46  2167  158.0  50.5    0\n",
       "586  17  女    0.00  11.15  149.0    28   50  2498  158.0  48.0    0\n",
       "587  17  女    3.46   9.60  170.0     6   41  2824  163.0  59.5    0\n",
       "588  17  女    3.51   9.60  150.0    24   41  2255  158.0  49.0    0\n",
       "589  17  女    4.00  10.18  150.0    13   36  2937  161.0  55.7    0\n",
       "590  17  女    3.45  10.18  152.0    15   35  2592  165.0  48.6    0\n",
       "591  17  女    4.01   9.67  165.0    10   41  1829  154.0  43.6    0\n",
       "592  17  女    4.48   9.09  180.0    10   46  2962  162.0  55.3    0\n",
       "\n",
       "[593 rows x 11 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.read_excel('./18级高一体测成绩汇总.xls',sheetname=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 评分标准加载，pd.read_excel('./体侧成绩评分表.xls',header = [0,1])，header=[0,1]表示多层列索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th>男肺活量</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女肺活量</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男50米跑</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女50米跑</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男体前屈</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女跳远</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男引体</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女仰卧</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男1000米跑</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女800米跑</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>...</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4540</th>\n",
       "      <td>100</td>\n",
       "      <td>3150</td>\n",
       "      <td>100</td>\n",
       "      <td>7.1</td>\n",
       "      <td>100</td>\n",
       "      <td>7.8</td>\n",
       "      <td>100</td>\n",
       "      <td>23.6</td>\n",
       "      <td>100</td>\n",
       "      <td>24.2</td>\n",
       "      <td>...</td>\n",
       "      <td>204</td>\n",
       "      <td>100</td>\n",
       "      <td>16.0</td>\n",
       "      <td>100</td>\n",
       "      <td>53</td>\n",
       "      <td>100</td>\n",
       "      <td>3'30\"</td>\n",
       "      <td>100</td>\n",
       "      <td>3'24\"</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4420</th>\n",
       "      <td>95</td>\n",
       "      <td>3100</td>\n",
       "      <td>95</td>\n",
       "      <td>7.2</td>\n",
       "      <td>95</td>\n",
       "      <td>7.9</td>\n",
       "      <td>95</td>\n",
       "      <td>21.5</td>\n",
       "      <td>95</td>\n",
       "      <td>22.5</td>\n",
       "      <td>...</td>\n",
       "      <td>198</td>\n",
       "      <td>95</td>\n",
       "      <td>15.0</td>\n",
       "      <td>95</td>\n",
       "      <td>51</td>\n",
       "      <td>95</td>\n",
       "      <td>3'35\"</td>\n",
       "      <td>95</td>\n",
       "      <td>3'30\"</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4300</th>\n",
       "      <td>90</td>\n",
       "      <td>3050</td>\n",
       "      <td>90</td>\n",
       "      <td>7.3</td>\n",
       "      <td>90</td>\n",
       "      <td>8.0</td>\n",
       "      <td>90</td>\n",
       "      <td>19.4</td>\n",
       "      <td>90</td>\n",
       "      <td>20.8</td>\n",
       "      <td>...</td>\n",
       "      <td>192</td>\n",
       "      <td>90</td>\n",
       "      <td>14.0</td>\n",
       "      <td>90</td>\n",
       "      <td>49</td>\n",
       "      <td>90</td>\n",
       "      <td>3'40\"</td>\n",
       "      <td>90</td>\n",
       "      <td>3'36\"</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4050</th>\n",
       "      <td>85</td>\n",
       "      <td>2900</td>\n",
       "      <td>85</td>\n",
       "      <td>7.4</td>\n",
       "      <td>85</td>\n",
       "      <td>8.3</td>\n",
       "      <td>85</td>\n",
       "      <td>17.2</td>\n",
       "      <td>85</td>\n",
       "      <td>19.1</td>\n",
       "      <td>...</td>\n",
       "      <td>185</td>\n",
       "      <td>85</td>\n",
       "      <td>13.0</td>\n",
       "      <td>85</td>\n",
       "      <td>46</td>\n",
       "      <td>85</td>\n",
       "      <td>3'47\"</td>\n",
       "      <td>85</td>\n",
       "      <td>3'43\"</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3800</th>\n",
       "      <td>80</td>\n",
       "      <td>2750</td>\n",
       "      <td>80</td>\n",
       "      <td>7.5</td>\n",
       "      <td>80</td>\n",
       "      <td>8.6</td>\n",
       "      <td>80</td>\n",
       "      <td>15.0</td>\n",
       "      <td>80</td>\n",
       "      <td>17.4</td>\n",
       "      <td>...</td>\n",
       "      <td>178</td>\n",
       "      <td>80</td>\n",
       "      <td>12.0</td>\n",
       "      <td>80</td>\n",
       "      <td>43</td>\n",
       "      <td>80</td>\n",
       "      <td>3'55\"</td>\n",
       "      <td>80</td>\n",
       "      <td>3'50\"</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3680</th>\n",
       "      <td>78</td>\n",
       "      <td>2650</td>\n",
       "      <td>78</td>\n",
       "      <td>7.7</td>\n",
       "      <td>78</td>\n",
       "      <td>8.8</td>\n",
       "      <td>78</td>\n",
       "      <td>13.6</td>\n",
       "      <td>78</td>\n",
       "      <td>16.1</td>\n",
       "      <td>...</td>\n",
       "      <td>175</td>\n",
       "      <td>78</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78</td>\n",
       "      <td>41</td>\n",
       "      <td>78</td>\n",
       "      <td>4'00\"</td>\n",
       "      <td>78</td>\n",
       "      <td>3'55\"</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3560</th>\n",
       "      <td>76</td>\n",
       "      <td>2550</td>\n",
       "      <td>76</td>\n",
       "      <td>7.9</td>\n",
       "      <td>76</td>\n",
       "      <td>9.0</td>\n",
       "      <td>76</td>\n",
       "      <td>12.2</td>\n",
       "      <td>76</td>\n",
       "      <td>14.8</td>\n",
       "      <td>...</td>\n",
       "      <td>172</td>\n",
       "      <td>76</td>\n",
       "      <td>11.0</td>\n",
       "      <td>76</td>\n",
       "      <td>39</td>\n",
       "      <td>76</td>\n",
       "      <td>4'05\"</td>\n",
       "      <td>76</td>\n",
       "      <td>4'00\"</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3440</th>\n",
       "      <td>74</td>\n",
       "      <td>2450</td>\n",
       "      <td>74</td>\n",
       "      <td>8.1</td>\n",
       "      <td>74</td>\n",
       "      <td>9.2</td>\n",
       "      <td>74</td>\n",
       "      <td>10.8</td>\n",
       "      <td>74</td>\n",
       "      <td>13.5</td>\n",
       "      <td>...</td>\n",
       "      <td>169</td>\n",
       "      <td>74</td>\n",
       "      <td>NaN</td>\n",
       "      <td>74</td>\n",
       "      <td>37</td>\n",
       "      <td>74</td>\n",
       "      <td>4'10\"</td>\n",
       "      <td>74</td>\n",
       "      <td>4'05\"</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3320</th>\n",
       "      <td>72</td>\n",
       "      <td>2350</td>\n",
       "      <td>72</td>\n",
       "      <td>8.3</td>\n",
       "      <td>72</td>\n",
       "      <td>9.4</td>\n",
       "      <td>72</td>\n",
       "      <td>9.4</td>\n",
       "      <td>72</td>\n",
       "      <td>12.2</td>\n",
       "      <td>...</td>\n",
       "      <td>166</td>\n",
       "      <td>72</td>\n",
       "      <td>10.0</td>\n",
       "      <td>72</td>\n",
       "      <td>35</td>\n",
       "      <td>72</td>\n",
       "      <td>4'15\"</td>\n",
       "      <td>72</td>\n",
       "      <td>4'10\"</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3200</th>\n",
       "      <td>70</td>\n",
       "      <td>2250</td>\n",
       "      <td>70</td>\n",
       "      <td>8.5</td>\n",
       "      <td>70</td>\n",
       "      <td>9.6</td>\n",
       "      <td>70</td>\n",
       "      <td>8.0</td>\n",
       "      <td>70</td>\n",
       "      <td>10.9</td>\n",
       "      <td>...</td>\n",
       "      <td>163</td>\n",
       "      <td>70</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70</td>\n",
       "      <td>33</td>\n",
       "      <td>70</td>\n",
       "      <td>4'20\"</td>\n",
       "      <td>70</td>\n",
       "      <td>4'15\"</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3080</th>\n",
       "      <td>68</td>\n",
       "      <td>2150</td>\n",
       "      <td>68</td>\n",
       "      <td>8.7</td>\n",
       "      <td>68</td>\n",
       "      <td>9.8</td>\n",
       "      <td>68</td>\n",
       "      <td>6.6</td>\n",
       "      <td>68</td>\n",
       "      <td>9.6</td>\n",
       "      <td>...</td>\n",
       "      <td>160</td>\n",
       "      <td>68</td>\n",
       "      <td>9.0</td>\n",
       "      <td>68</td>\n",
       "      <td>31</td>\n",
       "      <td>68</td>\n",
       "      <td>4'25\"</td>\n",
       "      <td>68</td>\n",
       "      <td>4'20\"</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2960</th>\n",
       "      <td>66</td>\n",
       "      <td>2050</td>\n",
       "      <td>66</td>\n",
       "      <td>8.9</td>\n",
       "      <td>66</td>\n",
       "      <td>10.0</td>\n",
       "      <td>66</td>\n",
       "      <td>5.2</td>\n",
       "      <td>66</td>\n",
       "      <td>8.3</td>\n",
       "      <td>...</td>\n",
       "      <td>157</td>\n",
       "      <td>66</td>\n",
       "      <td>NaN</td>\n",
       "      <td>66</td>\n",
       "      <td>29</td>\n",
       "      <td>66</td>\n",
       "      <td>4'30\"</td>\n",
       "      <td>66</td>\n",
       "      <td>4'25\"</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2840</th>\n",
       "      <td>64</td>\n",
       "      <td>1950</td>\n",
       "      <td>64</td>\n",
       "      <td>9.1</td>\n",
       "      <td>64</td>\n",
       "      <td>10.2</td>\n",
       "      <td>64</td>\n",
       "      <td>3.8</td>\n",
       "      <td>64</td>\n",
       "      <td>7.0</td>\n",
       "      <td>...</td>\n",
       "      <td>154</td>\n",
       "      <td>64</td>\n",
       "      <td>8.0</td>\n",
       "      <td>64</td>\n",
       "      <td>27</td>\n",
       "      <td>64</td>\n",
       "      <td>4'35\"</td>\n",
       "      <td>64</td>\n",
       "      <td>4'30\"</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2720</th>\n",
       "      <td>62</td>\n",
       "      <td>1850</td>\n",
       "      <td>62</td>\n",
       "      <td>9.3</td>\n",
       "      <td>62</td>\n",
       "      <td>10.4</td>\n",
       "      <td>62</td>\n",
       "      <td>2.4</td>\n",
       "      <td>62</td>\n",
       "      <td>5.7</td>\n",
       "      <td>...</td>\n",
       "      <td>151</td>\n",
       "      <td>62</td>\n",
       "      <td>NaN</td>\n",
       "      <td>62</td>\n",
       "      <td>25</td>\n",
       "      <td>62</td>\n",
       "      <td>4'40\"</td>\n",
       "      <td>62</td>\n",
       "      <td>4'35\"</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2600</th>\n",
       "      <td>60</td>\n",
       "      <td>1750</td>\n",
       "      <td>60</td>\n",
       "      <td>9.5</td>\n",
       "      <td>60</td>\n",
       "      <td>10.6</td>\n",
       "      <td>60</td>\n",
       "      <td>1.0</td>\n",
       "      <td>60</td>\n",
       "      <td>4.4</td>\n",
       "      <td>...</td>\n",
       "      <td>148</td>\n",
       "      <td>60</td>\n",
       "      <td>7.0</td>\n",
       "      <td>60</td>\n",
       "      <td>23</td>\n",
       "      <td>60</td>\n",
       "      <td>4'45\"</td>\n",
       "      <td>60</td>\n",
       "      <td>4'40\"</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2470</th>\n",
       "      <td>50</td>\n",
       "      <td>1710</td>\n",
       "      <td>50</td>\n",
       "      <td>9.7</td>\n",
       "      <td>50</td>\n",
       "      <td>10.8</td>\n",
       "      <td>50</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50</td>\n",
       "      <td>3.6</td>\n",
       "      <td>...</td>\n",
       "      <td>143</td>\n",
       "      <td>50</td>\n",
       "      <td>6.0</td>\n",
       "      <td>50</td>\n",
       "      <td>21</td>\n",
       "      <td>50</td>\n",
       "      <td>5'05\"</td>\n",
       "      <td>50</td>\n",
       "      <td>4'50\"</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2340</th>\n",
       "      <td>40</td>\n",
       "      <td>1670</td>\n",
       "      <td>40</td>\n",
       "      <td>9.9</td>\n",
       "      <td>40</td>\n",
       "      <td>11.0</td>\n",
       "      <td>40</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>40</td>\n",
       "      <td>2.8</td>\n",
       "      <td>...</td>\n",
       "      <td>138</td>\n",
       "      <td>40</td>\n",
       "      <td>5.0</td>\n",
       "      <td>40</td>\n",
       "      <td>19</td>\n",
       "      <td>40</td>\n",
       "      <td>5'25\"</td>\n",
       "      <td>40</td>\n",
       "      <td>5'00\"</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2210</th>\n",
       "      <td>30</td>\n",
       "      <td>1630</td>\n",
       "      <td>30</td>\n",
       "      <td>10.1</td>\n",
       "      <td>30</td>\n",
       "      <td>11.2</td>\n",
       "      <td>30</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>30</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>133</td>\n",
       "      <td>30</td>\n",
       "      <td>4.0</td>\n",
       "      <td>30</td>\n",
       "      <td>17</td>\n",
       "      <td>30</td>\n",
       "      <td>5'45\"</td>\n",
       "      <td>30</td>\n",
       "      <td>5'10\"</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2080</th>\n",
       "      <td>20</td>\n",
       "      <td>1590</td>\n",
       "      <td>20</td>\n",
       "      <td>10.3</td>\n",
       "      <td>20</td>\n",
       "      <td>11.4</td>\n",
       "      <td>20</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>20</td>\n",
       "      <td>1.2</td>\n",
       "      <td>...</td>\n",
       "      <td>128</td>\n",
       "      <td>20</td>\n",
       "      <td>3.0</td>\n",
       "      <td>20</td>\n",
       "      <td>15</td>\n",
       "      <td>20</td>\n",
       "      <td>6'05\"</td>\n",
       "      <td>20</td>\n",
       "      <td>5'20\"</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950</th>\n",
       "      <td>10</td>\n",
       "      <td>1550</td>\n",
       "      <td>10</td>\n",
       "      <td>10.5</td>\n",
       "      <td>10</td>\n",
       "      <td>11.6</td>\n",
       "      <td>10</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>10</td>\n",
       "      <td>0.4</td>\n",
       "      <td>...</td>\n",
       "      <td>123</td>\n",
       "      <td>10</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "      <td>10</td>\n",
       "      <td>6'25\"</td>\n",
       "      <td>10</td>\n",
       "      <td>5'30\"</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "男肺活量 男肺活量  女肺活量      男50米跑      女50米跑       男体前屈       女体前屈 ...   女跳远       \\\n",
       "成绩     分数    成绩   分数    成绩   分数    成绩   分数    成绩   分数    成绩 ...    成绩   分数   \n",
       "4540  100  3150  100   7.1  100   7.8  100  23.6  100  24.2 ...   204  100   \n",
       "4420   95  3100   95   7.2   95   7.9   95  21.5   95  22.5 ...   198   95   \n",
       "4300   90  3050   90   7.3   90   8.0   90  19.4   90  20.8 ...   192   90   \n",
       "4050   85  2900   85   7.4   85   8.3   85  17.2   85  19.1 ...   185   85   \n",
       "3800   80  2750   80   7.5   80   8.6   80  15.0   80  17.4 ...   178   80   \n",
       "3680   78  2650   78   7.7   78   8.8   78  13.6   78  16.1 ...   175   78   \n",
       "3560   76  2550   76   7.9   76   9.0   76  12.2   76  14.8 ...   172   76   \n",
       "3440   74  2450   74   8.1   74   9.2   74  10.8   74  13.5 ...   169   74   \n",
       "3320   72  2350   72   8.3   72   9.4   72   9.4   72  12.2 ...   166   72   \n",
       "3200   70  2250   70   8.5   70   9.6   70   8.0   70  10.9 ...   163   70   \n",
       "3080   68  2150   68   8.7   68   9.8   68   6.6   68   9.6 ...   160   68   \n",
       "2960   66  2050   66   8.9   66  10.0   66   5.2   66   8.3 ...   157   66   \n",
       "2840   64  1950   64   9.1   64  10.2   64   3.8   64   7.0 ...   154   64   \n",
       "2720   62  1850   62   9.3   62  10.4   62   2.4   62   5.7 ...   151   62   \n",
       "2600   60  1750   60   9.5   60  10.6   60   1.0   60   4.4 ...   148   60   \n",
       "2470   50  1710   50   9.7   50  10.8   50   0.0   50   3.6 ...   143   50   \n",
       "2340   40  1670   40   9.9   40  11.0   40  -1.0   40   2.8 ...   138   40   \n",
       "2210   30  1630   30  10.1   30  11.2   30  -2.0   30   2.0 ...   133   30   \n",
       "2080   20  1590   20  10.3   20  11.4   20  -3.0   20   1.2 ...   128   20   \n",
       "1950   10  1550   10  10.5   10  11.6   10  -4.0   10   0.4 ...   123   10   \n",
       "\n",
       "男肺活量   男引体      女仰卧      男1000米跑      女800米跑       \n",
       "成绩      成绩   分数  成绩   分数      成绩   分数     成绩   分数  \n",
       "4540  16.0  100  53  100   3'30\"  100  3'24\"  100  \n",
       "4420  15.0   95  51   95   3'35\"   95  3'30\"   95  \n",
       "4300  14.0   90  49   90   3'40\"   90  3'36\"   90  \n",
       "4050  13.0   85  46   85   3'47\"   85  3'43\"   85  \n",
       "3800  12.0   80  43   80   3'55\"   80  3'50\"   80  \n",
       "3680   NaN   78  41   78   4'00\"   78  3'55\"   78  \n",
       "3560  11.0   76  39   76   4'05\"   76  4'00\"   76  \n",
       "3440   NaN   74  37   74   4'10\"   74  4'05\"   74  \n",
       "3320  10.0   72  35   72   4'15\"   72  4'10\"   72  \n",
       "3200   NaN   70  33   70   4'20\"   70  4'15\"   70  \n",
       "3080   9.0   68  31   68   4'25\"   68  4'20\"   68  \n",
       "2960   NaN   66  29   66   4'30\"   66  4'25\"   66  \n",
       "2840   8.0   64  27   64   4'35\"   64  4'30\"   64  \n",
       "2720   NaN   62  25   62   4'40\"   62  4'35\"   62  \n",
       "2600   7.0   60  23   60   4'45\"   60  4'40\"   60  \n",
       "2470   6.0   50  21   50   5'05\"   50  4'50\"   50  \n",
       "2340   5.0   40  19   40   5'25\"   40  5'00\"   40  \n",
       "2210   4.0   30  17   30   5'45\"   30  5'10\"   30  \n",
       "2080   3.0   20  15   20   6'05\"   20  5'20\"   20  \n",
       "1950   2.0   10  13   10   6'25\"   10  5'30\"   10  \n",
       "\n",
       "[20 rows x 23 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = pd.read_excel('.\\体侧成绩评分表.xls',header=[0,1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据类型转换"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  男1000米跑，数据类型是str，并且是4’26这种形式，需要变成float类型的值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "Can only use .str accessor with string values, which use np.object_ dtype in pandas",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-41-7d72c81bf785>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdf1\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'男1000米跑'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf1\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'男1000米跑'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mstr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mreplace\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"'\"\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m\".\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      2\u001b[0m \u001b[0mdf1\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'男1000米跑'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mto_numeric\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdf1\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'男1000米跑'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0merrors\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'coerce'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfillna\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[0mdf1\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdtypes\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36m__getattr__\u001b[1;34m(self, name)\u001b[0m\n\u001b[0;32m   2964\u001b[0m         if (name in self._internal_names_set or name in self._metadata or\n\u001b[0;32m   2965\u001b[0m                 name in self._accessors):\n\u001b[1;32m-> 2966\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mobject\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__getattribute__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mname\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2967\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2968\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mname\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_info_axis\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\base.py\u001b[0m in \u001b[0;36m__get__\u001b[1;34m(self, instance, owner)\u001b[0m\n\u001b[0;32m    241\u001b[0m             \u001b[1;31m# this ensures that Series.str.<method> is well defined\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    242\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0maccessor_cls\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 243\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconstruct_accessor\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0minstance\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    244\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    245\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m__set__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0minstance\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\strings.py\u001b[0m in \u001b[0;36m_make_str_accessor\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1907\u001b[0m             \u001b[1;31m# (instead of test for object dtype), but that isn't practical for\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1908\u001b[0m             \u001b[1;31m# performance reasons until we have a str dtype (GH 9343)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1909\u001b[1;33m             raise AttributeError(\"Can only use .str accessor with string \"\n\u001b[0m\u001b[0;32m   1910\u001b[0m                                  \u001b[1;34m\"values, which use np.object_ dtype in \"\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1911\u001b[0m                                  \"pandas\")\n",
      "\u001b[1;31mAttributeError\u001b[0m: Can only use .str accessor with string values, which use np.object_ dtype in pandas"
     ]
    }
   ],
   "source": [
    "df1['男1000米跑'] = df1['男1000米跑'].str.replace(\"'\",\".\")\n",
    "df1['男1000米跑'] = pd.to_numeric(df1['男1000米跑'],errors='coerce').fillna(0)\n",
    "df1[['男50米跑','男跳远','男体前屈','男引体','男肺活量','身高','体重','BMI']]=df1[['男50米跑','男跳远','男体前屈','男引体','男肺活量','身高','体重','BMI']].astype(float)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  评分标准中男1000米跑和女800米跑的成绩都是4‘10’‘这种形式，需要转化为float类型值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "'女800米跑'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32mD:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexes\\base.py\u001b[0m in \u001b[0;36mget_loc\u001b[1;34m(self, key, method, tolerance)\u001b[0m\n\u001b[0;32m   2392\u001b[0m             \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2393\u001b[1;33m                 \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2394\u001b[0m             \u001b[1;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc (pandas\\_libs\\index.c:5239)\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc (pandas\\_libs\\index.c:5085)\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item (pandas\\_libs\\hashtable.c:20405)\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item (pandas\\_libs\\hashtable.c:20359)\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m: '女800米跑'",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-43-52e2db1b73cd>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdf1\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'女800米跑'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf1\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'女800米跑'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mastype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfloat\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32mD:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36m__getitem__\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m   2060\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_getitem_multilevel\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2061\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2062\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_getitem_column\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2063\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2064\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_getitem_column\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36m_getitem_column\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m   2067\u001b[0m         \u001b[1;31m# get column\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2068\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mis_unique\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2069\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_get_item_cache\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2070\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2071\u001b[0m         \u001b[1;31m# duplicate columns & possible reduce dimensionality\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36m_get_item_cache\u001b[1;34m(self, item)\u001b[0m\n\u001b[0;32m   1532\u001b[0m         \u001b[0mres\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mcache\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mitem\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1533\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mres\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1534\u001b[1;33m             \u001b[0mvalues\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_data\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mitem\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1535\u001b[0m             \u001b[0mres\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_box_item_values\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mitem\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mvalues\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1536\u001b[0m             \u001b[0mcache\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mitem\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mres\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\internals.py\u001b[0m in \u001b[0;36mget\u001b[1;34m(self, item, fastpath)\u001b[0m\n\u001b[0;32m   3588\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3589\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0misnull\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mitem\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 3590\u001b[1;33m                 \u001b[0mloc\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mitems\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mitem\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   3591\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3592\u001b[0m                 \u001b[0mindexer\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0marange\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mlen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mitems\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0misnull\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mitems\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mD:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexes\\base.py\u001b[0m in \u001b[0;36mget_loc\u001b[1;34m(self, key, method, tolerance)\u001b[0m\n\u001b[0;32m   2393\u001b[0m                 \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2394\u001b[0m             \u001b[1;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 2395\u001b[1;33m                 \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_maybe_cast_indexer\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   2396\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   2397\u001b[0m         \u001b[0mindexer\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget_indexer\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmethod\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mtolerance\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mtolerance\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc (pandas\\_libs\\index.c:5239)\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc (pandas\\_libs\\index.c:5085)\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item (pandas\\_libs\\hashtable.c:20405)\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;32mpandas\\_libs\\hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item (pandas\\_libs\\hashtable.c:20359)\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m: '女800米跑'"
     ]
    }
   ],
   "source": [
    "df1['女800米跑'] = df1['女800米跑'].astype(float)\n",
    "df1[['女50米跑','女跳远','女体前屈','女仰卧','女肺活量','身高','体重','BMI']]=df1[['女50米跑','女跳远','女体前屈','女仰卧','女肺活量','身高','体重','BMI']].astype(float)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 其他所有数值类型的值，都要转换为float类型的值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df3' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-30-ed04ebdb9e6a>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdf3\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf3\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mreplace\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"'\"\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m\".\"\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mregex\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      2\u001b[0m \u001b[0mdf3\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf3\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mreplace\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'\"'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m\"\"\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mregex\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[0mdf3\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf3\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mastype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfloat\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'df3' is not defined"
     ]
    }
   ],
   "source": [
    "df3 = df3.replace(\"'\",\".\",regex = True)\n",
    "df3 = df3.replace('\"',\"\",regex = True)\n",
    "df3 = df3.astype(float)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 对体测成绩进行分数转换，跑步类（越小越好）；跳远、体前屈（越大越好）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-44-30ba655c8330>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      8\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[1;36m7\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      9\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 10\u001b[1;33m df1[['男50米跑分数','男跳远分数','男体前屈分数','男引体分数','男肺活量分数']]=df.transform(\n\u001b[0m\u001b[0;32m     11\u001b[0m     {'男50米跑':cond,'男跳远':cond,'男体前屈':cond,'男引体':cond,'男肺活量':cond})\n",
      "\u001b[1;31mNameError\u001b[0m: name 'df' is not defined"
     ]
    }
   ],
   "source": [
    "#根据各项体能指标，制定分数，此处虚拟为各项体能指标有一样的计分规则\n",
    "def cond(x):\n",
    "    if (a>0) & (a<5):\n",
    "        return 3\n",
    "    elif (a>5) & (a<7):\n",
    "        return 5\n",
    "    else:\n",
    "        return 7\n",
    "         \n",
    "df1[['男50米跑分数','男跳远分数','男体前屈分数','男引体分数','男肺活量分数']]=df.transform(\n",
    "    {'男50米跑':cond,'男跳远':cond,'男体前屈':cond,'男引体':cond,'男肺活量':cond})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-45-94a0d5bb244e>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      8\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[1;36m7\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      9\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 10\u001b[1;33m df1[['女50米跑分数','女跳远分数','女体前屈分数','女仰卧分数','女肺活量分数']]=df.transform(\n\u001b[0m\u001b[0;32m     11\u001b[0m     {'女50米跑':cond1,'女跳远':cond1,'女体前屈':cond1,'女仰卧':cond1,'女肺活量':cond1})\n",
      "\u001b[1;31mNameError\u001b[0m: name 'df' is not defined"
     ]
    }
   ],
   "source": [
    "#根据各项体能指标，制定分数，此处虚拟为各项体能指标有一样的计分规则\n",
    "def cond1(x):\n",
    "    if (a>0) & (a<5):\n",
    "        return 3\n",
    "    elif (a>5) & (a<7):\n",
    "        return 5\n",
    "    else:\n",
    "        return 7\n",
    "         \n",
    "df1[['女50米跑分数','女跳远分数','女体前屈分数','女仰卧分数','女肺活量分数']]=df.transform(\n",
    "    {'女50米跑':cond1,'女跳远':cond1,'女体前屈':cond1,'女仰卧':cond1,'女肺活量':cond1})"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
